/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package mapping;
import java.sql.ResultSet;
import mapping.SqlLib;
import entreprise.Session;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Quoc-Khai
 */
public class DBSession {
    public Session getSession(String sessionID){
        Session iSession = new Session();
        String selectQuery;
        selectQuery = "SELECT * FROM session WHERE sessionID="+sessionID;       
        SqlLib connec = new SqlLib();
        if (connec.initConnection()) {
            ResultSet sqlResultSet = connec.getDataFromQuery(selectQuery);
            
            try {
                while(sqlResultSet.next()) {
                    iSession.setSessionID(sqlResultSet.getString("sessionID"));
                    iSession.setSessionDate(sqlResultSet.getDate("sessionDate"));
                    iSession.setDoctor(sqlResultSet.getString("doctor"));
                    iSession.setRequire(sqlResultSet.getString("require"));   
                    iSession.setDiagnostic(sqlResultSet.getString("diagnostic"));
                    iSession.setPatientID(sqlResultSet.getString("patientID"));
                }
            } catch (SQLException ex) {
                Logger.getLogger(DBSession.class.getName()).log(Level.SEVERE, null, ex);
                return null;
            }
        } else {
//            System.out.println("Session not found!");
              return null;
        } 
        return iSession;
    }
    public boolean createSession(Session session){
        //TODO;
        java.sql.Date sqlDate = new java.sql.Date(session.getSessionDate().getTime());
         String insertQuery;
        insertQuery = "INSERT INTO session (sessionDate, doctor, `require`, diagnostic,patientID) VALUES ('" + sqlDate + "','" + session.getDoctor()+ "','" + session.getRequire()+ "','" + session.getDiagnostic()+ "',"+session.getPatientID()+")";
        SqlLib connec = new SqlLib();
        
        if (connec.initConnection()) {
            connec.execQuery(insertQuery);
//            System.out.println("Insert Session success!");
            return true;
        }
        return false;
    }
    public boolean updateSession(Session session){
        //TODO
        java.sql.Date sqlDate = new java.sql.Date(session.getSessionDate().getTime());
        String updateQuery;
        updateQuery = "UPDATE session SET sessionDate='" + sqlDate+ 
                "', doctor='" + session.getDoctor()+
                "',`require`='" + session.getRequire()+ 
                "',diagnostic='" + session.getDiagnostic()+ 
                "',patientID="+session.getPatientID()+
                " WHERE sessionID="+session.getSessionID();
        
        SqlLib connec = new SqlLib();
        
        if (connec.initConnection()) {
            connec.execQuery(updateQuery);
//            System.out.println("Update Session success!");
            return true;
        }
        return false;
    }
    public boolean deleteSession(String sessionID){
        //TODO
        String deleteQuery;
        deleteQuery = "DELETE FROM session WHERE sessionID="+sessionID; 
        SqlLib connec = new SqlLib();
        
        if (connec.initConnection()) {
            connec.execQuery(deleteQuery);
//            System.out.println("Delete Session success!");
            return true;
        }
        return false;
    }    
    public ArrayList<Session> getSessions(String patientID){
        ArrayList<Session> sessions=new ArrayList<>();
        String selectQuery;
        selectQuery = "SELECT * FROM session WHERE patientID="+patientID;       
        SqlLib connec = new SqlLib();
        if (connec.initConnection()) {
            ResultSet sqlResultSet = connec.getDataFromQuery(selectQuery);            
            try {
                while(sqlResultSet.next()) {
                    Session iSession=new Session();
                    iSession.setSessionID(sqlResultSet.getString("sessionID"));
                    iSession.setSessionDate(sqlResultSet.getDate("sessionDate"));
                    iSession.setDoctor(sqlResultSet.getString("doctor"));
                    iSession.setRequire(sqlResultSet.getString("require"));   
                    iSession.setDiagnostic(sqlResultSet.getString("diagnostic"));
                    iSession.setPatientID(sqlResultSet.getString("patientID"));
                    sessions.add(iSession);
                }
            } catch (SQLException ex) {
                Logger.getLogger(DBSession.class.getName()).log(Level.SEVERE, null, ex);
                return null;
            }
        } else {
//            System.out.println("Session not found!");
              return null;
        }       
        return sessions;
    }    

    public int getLatestSessionID() {
        String selectQuery= "SELECT * FROM session order by sessionID desc limit 1";
        SqlLib connec = new SqlLib();
        if (connec.initConnection()) {
            ResultSet sqlResultSet = connec.getDataFromQuery(selectQuery);            
            try {
                while(sqlResultSet.next()) {
                    return sqlResultSet.getInt("sessionID");
                }                
            } catch (SQLException ex) {
                Logger.getLogger(DBSession.class.getName()).log(Level.SEVERE, null, ex);
                return 0;
            }
            connec.closeConnection();
        } else {
//            System.out.println("Session not found!");
              return 0;
        }
        return 0;
    }
}
